﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Collections.Generic;

/// <summary>
/// Adatbázis lekérdező függvények
/// </summary>
public class ManageDatabase
{
    //private static ManageDatabase instance;
    //private ManageDatabase() { }
    //public static ManageDatabase Instance {
    //    get 
    //    {
    //        if (instance == null)
    //        {
    //            instance = new ManageDatabase();
    //        }
    //        return instance;
    //    }
    //}
    public ManageDatabase() { }

    public void TestConnect() {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            try{
                connection.Open();
            }catch (Exception e){
                Alert.Show("Error in connecting! " + e.ToString());
            }
            finally{
                connection.Close();
            }
        }       
    }

    public ListItemCollection GetGenres()
    {
        ListItemCollection retVal = new ListItemCollection();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT id, genre_hu FROM genres WHERE status=1 ORDER BY genre_hu", connection);
                connection.Open();
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        string id = sdr.GetInt32(0).ToString();
                        string genre_hu = sdr.GetString(1);
                        retVal.Add(new ListItem(genre_hu, id));
                    }
                }
            }
            catch (Exception e)
            {
                Alert.Show("Error in connecting! " + e.ToString());
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
        return retVal;
    }

    public ListItemCollection GetSeriesSimple()
    {
        ListItemCollection retVal = new ListItemCollection();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                command.CommandText = "SELECT id, title_hun FROM series WHERE status=1 ORDER BY title_hun";
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        string id = sdr.GetInt32(0).ToString();
                        string title_hun = sdr.GetString(1);
                        retVal.Add(new ListItem(title_hun, id));
                    }
                    sdr.Close();
                }
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
        return retVal;
    }

    public ListItemCollection GetSeasonsSimple(int series_id)
    {
        ListItemCollection retVal = new ListItemCollection();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {  
                connection.Open();
                command.CommandText = "SELECT id, season FROM seasons WHERE series_id=@series_id AND status=1 ORDER BY season";
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int, 4)).Value = series_id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        string id = sdr.GetInt32(0).ToString();
                        string season = sdr.GetInt32(1).ToString();
                        retVal.Add(new ListItem(season, id));
                    }
                    sdr.Close();
                }
            }
            catch (Exception)
            {     
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
        return retVal;
    }

    public ListItemCollection GetEpisodesSimple(int season_id)
    {
        ListItemCollection retVal = new ListItemCollection();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                command.CommandText = "SELECT id, episode FROM episodes WHERE season_id=@season_id AND status=1 ORDER BY episode";
                command.Parameters.Add(new SqlParameter("@season_id", SqlDbType.Int, 4)).Value = season_id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        string id = sdr.GetInt32(0).ToString();
                        string episode = sdr.GetInt32(1).ToString();
                        retVal.Add(new ListItem(episode, id));
                    }
                    sdr.Close();
                }
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
        return retVal;
    }

    public List<Serie> GetSeriesPreview()
    {
        List<Serie> series = new List<Serie>();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                command.CommandText = "SELECT id, title_hun, title_orig, directors, writers, stars, release_date, description, img_url, " +
                    "video_url, user_id, status, created_date, last_modified_date FROM series ORDER BY title_hun";	
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        Serie s = new Serie();
                        s.id = Convert.ToInt32(sdr["id"]);
                        s.titleHun = sdr["title_hun"].ToString();
                        if (sdr["title_orig"] != null) s.titleOrig = sdr["title_orig"].ToString();
                        if (sdr["directors"] != null) s.directors = sdr["directors"].ToString();
                        if (sdr["writers"] != null) s.writers = sdr["writers"].ToString();
                        if (sdr["stars"] != null) s.stars = sdr["stars"].ToString();
                        if (sdr["release_date"] != null) s.releaseDate = Convert.ToDateTime(sdr["release_date"]);
                        if (sdr["description"] != null) s.description = sdr["description"].ToString();
                        if (sdr["img_url"] != null) s.imgUrl = sdr["img_url"].ToString();
                        if (sdr["video_url"] != null) s.videoUrl = sdr["video_url"].ToString();
                        s.status = Convert.ToInt32(sdr["status"]);
                        s.userID = Convert.ToInt32(sdr["user_id"]);
                        s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                        s.lastModifiedDate = Convert.ToDateTime(sdr["last_modified_date"]);
                        series.Add(s);
                    }
                    sdr.Close();
                }
                return series;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public List<Serie> GetSeriesPreviewTop3()
    {
        List<Serie> series = new List<Serie>();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                command.CommandText = "SELECT TOP 3 id, title_hun, title_orig, directors, writers, stars, release_date, description, img_url, " +
                    "video_url, user_id, status, created_date, last_modified_date FROM series WHERE status=1 ORDER BY created_date DESC";
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        Serie s = new Serie();
                        s.id = Convert.ToInt32(sdr["id"]);
                        s.titleHun = sdr["title_hun"].ToString();
                        if (sdr["title_orig"] != null) s.titleOrig = sdr["title_orig"].ToString();
                        if (sdr["directors"] != null) s.directors = sdr["directors"].ToString();
                        if (sdr["writers"] != null) s.writers = sdr["writers"].ToString();
                        if (sdr["stars"] != null) s.stars = sdr["stars"].ToString();
                        if (sdr["release_date"] != null) s.releaseDate = Convert.ToDateTime(sdr["release_date"]);
                        if (sdr["description"] != null) s.description = sdr["description"].ToString();
                        if (sdr["img_url"] != null) s.imgUrl = sdr["img_url"].ToString();
                        if (sdr["video_url"] != null) s.videoUrl = sdr["video_url"].ToString();
                        s.status = Convert.ToInt32(sdr["status"]);
                        s.userID = Convert.ToInt32(sdr["user_id"]);
                        s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                        s.lastModifiedDate = Convert.ToDateTime(sdr["last_modified_date"]);
                        series.Add(s);
                    }
                    sdr.Close();
                }
                return series;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public List<Serie> GetSeriesPreviewTop10ByRating()
    {
        List<Serie> series = new List<Serie>();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                command.CommandText = "SELECT TOP 10 id, title_hun, title_orig, directors, writers, stars, release_date, description, img_url, " +
                    "video_url, user_id, status, created_date, last_modified_date FROM series WHERE status=1 ORDER BY created_date DESC";
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        Serie s = new Serie();
                        s.id = Convert.ToInt32(sdr["id"]);
                        s.titleHun = sdr["title_hun"].ToString();
                        if (sdr["title_orig"] != null) s.titleOrig = sdr["title_orig"].ToString();
                        if (sdr["directors"] != null) s.directors = sdr["directors"].ToString();
                        if (sdr["writers"] != null) s.writers = sdr["writers"].ToString();
                        if (sdr["stars"] != null) s.stars = sdr["stars"].ToString();
                        if (sdr["release_date"] != null) s.releaseDate = Convert.ToDateTime(sdr["release_date"]);
                        if (sdr["description"] != null) s.description = sdr["description"].ToString();
                        if (sdr["img_url"] != null) s.imgUrl = sdr["img_url"].ToString();
                        if (sdr["video_url"] != null) s.videoUrl = sdr["video_url"].ToString();
                        s.status = Convert.ToInt32(sdr["status"]);
                        s.userID = Convert.ToInt32(sdr["user_id"]);
                        s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                        s.lastModifiedDate = Convert.ToDateTime(sdr["last_modified_date"]);
                        series.Add(s);
                    }
                    sdr.Close();
                }
                return series;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public List<Season> GetSeasonsPreview(int series_id)
    {
        List<Season> seasons = new List<Season>();

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                command.CommandText = "SELECT id, series_id, season, release_date, description, img_url,video_url, user_id, status, created_date, " +
                    "last_modified_date FROM seasons WHERE status=1 AND series_id=@series_id ORDER BY season";
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int, 4)).Value = series_id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        Season s = new Season();
                        s.id = Convert.ToInt32(sdr["id"]);
                        s.seriesId = Convert.ToInt32(sdr["series_id"]);
                        s.season = Convert.ToInt32(sdr["season"]);
                        if (sdr["release_date"] != null) s.releaseDate = Convert.ToDateTime(sdr["release_date"]);
                        if (sdr["description"] != null) s.description = sdr["description"].ToString();
                        if (sdr["img_url"] != null) s.imgUrl = sdr["img_url"].ToString();
                        if (sdr["video_url"] != null) s.videoUrl = sdr["video_url"].ToString();
                        s.userID = Convert.ToInt32(sdr["user_id"]);
                        s.status = Convert.ToInt32(sdr["status"]);
                        s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                        s.lastModifiedDate = Convert.ToDateTime(sdr["last_modified_date"]);
                        seasons.Add(s);
                    }
                    sdr.Close();
                }
                return seasons;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public List<Episode> GetEpisodesPreview(int season_id)
    {
        List<Episode> episodes = new List<Episode>();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                command.CommandText = "SELECT id, series_id, season_id, episode, title_hun, title_orig, release_date, description, length, img_url, video_url, user_id, " +
                    "status, created_date, last_modified_date FROM episodes WHERE status=1 AND season_id=@season_id ORDER BY episode";
                command.Parameters.Add(new SqlParameter("@season_id", SqlDbType.Int, 4)).Value = season_id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        Episode s = new Episode();
                        s.id = Convert.ToInt32(sdr["id"]);
                        s.seriesId = Convert.ToInt32(sdr["series_id"]);
                        s.seasonId = Convert.ToInt32(sdr["season_id"]);
                        s.episode = Convert.ToInt32(sdr["episode"]);
                        s.titleHun = sdr["title_hun"].ToString();
                        if (sdr["title_orig"] != null) s.titleOrig = sdr["title_orig"].ToString();
                        if (sdr["release_date"] != null) s.releaseDate = Convert.ToDateTime(sdr["release_date"]);
                        if (sdr["description"] != null) s.description = sdr["description"].ToString();
                        if (sdr["length"] != null) s.length = Convert.ToInt32(sdr["length"]);
                        if (sdr["img_url"] != null) s.imgUrl = sdr["img_url"].ToString();
                        if (sdr["video_url"] != null) s.videoUrl = sdr["video_url"].ToString();
                        s.userID = Convert.ToInt32(sdr["user_id"]);
                        s.status = Convert.ToInt32(sdr["status"]);
                        s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                        s.lastModifiedDate = Convert.ToDateTime(sdr["last_modified_date"]);
                        episodes.Add(s);
                    }
                    sdr.Close();
                }
                return episodes;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Serie GetWholeSerie(int series_id, bool getAllStatus)
    {
        Serie s = new Serie();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();

                string status = "";
                if (!getAllStatus) {
                    status = "status=1 AND";
                }
                command.CommandText = "SELECT id, title_hun, title_orig, directors, writers, stars, release_date, description, img_url, " +
                    "video_url, user_id, status, created_date, last_modified_date FROM series WHERE " + status + " id=@id ORDER BY title_hun";
                command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4)).Value = series_id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        s.id = Convert.ToInt32(sdr["id"]);
                        s.titleHun = sdr["title_hun"].ToString();
                        if (sdr["title_orig"] != null) s.titleOrig = sdr["title_orig"].ToString();
                        if (sdr["directors"] != null) s.directors = sdr["directors"].ToString();
                        if (sdr["writers"] != null) s.writers = sdr["writers"].ToString();
                        if (sdr["stars"] != null) s.stars = sdr["stars"].ToString();
                        if (sdr["release_date"] != null) s.releaseDate = Convert.ToDateTime(sdr["release_date"]);
                        if (sdr["description"] != null) s.description = sdr["description"].ToString();
                        if (sdr["img_url"] != null) s.imgUrl = sdr["img_url"].ToString();
                        if (sdr["video_url"] != null) s.videoUrl = sdr["video_url"].ToString();
                        s.userID = Convert.ToInt32(sdr["user_id"]);
                        s.status = Convert.ToInt32(sdr["status"]);
                        s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                        s.lastModifiedDate = Convert.ToDateTime(sdr["last_modified_date"]);
                    }
                    sdr.Close();
                }

                command.CommandText = "SELECT g.id AS id, genre_hu FROM genres g, genre_index gi WHERE gi.genre_id = g.id and gi.series_id=@series_id ORDER BY genre_hu";
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int, 4)).Value = series_id;
                command.Prepare();
                sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    s.genres = new ListItemCollection();
                    while (sdr.Read())
                    {
                        s.genres.Add(new ListItem(sdr["genre_hu"].ToString(), sdr["id"].ToString()));
                    }
                    sdr.Close();
                }

                return s;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Season GetWholeSeason(int season_id, bool getAllStatus)
    {
        Season s = new Season();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                string status = "";
                if (!getAllStatus)
                {
                    status = "status=1 AND";
                }
                command.CommandText = "SELECT id, series_id, season, release_date, description, img_url,video_url, user_id, status, created_date, " +
                    "last_modified_date FROM seasons WHERE " + status + " id=@season_id ORDER BY season";
                command.Parameters.Add(new SqlParameter("@season_id", SqlDbType.Int, 4)).Value = season_id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        s.id = Convert.ToInt32(sdr["id"]);
                        s.seriesId = Convert.ToInt32(sdr["series_id"]);
                        s.season = Convert.ToInt32(sdr["season"]);
                        if (sdr["release_date"] != null) s.releaseDate = Convert.ToDateTime(sdr["release_date"]);
                        if (sdr["description"] != null) s.description = sdr["description"].ToString();
                        if (sdr["img_url"] != null) s.imgUrl = sdr["img_url"].ToString();
                        if (sdr["video_url"] != null) s.videoUrl = sdr["video_url"].ToString();
                        s.userID = Convert.ToInt32(sdr["user_id"]);
                        s.status = Convert.ToInt32(sdr["status"]);
                        s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                        s.lastModifiedDate = Convert.ToDateTime(sdr["last_modified_date"]);
                    }
                    sdr.Close();
                }

                return s;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Episode GetWholeEpisode(int episode_id, bool getAllStatus)
    {
        Episode s = new Episode();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                string status = "";
                if (!getAllStatus)
                {
                    status = "status=1 AND";
                }
                command.CommandText = "SELECT id, series_id, season_id, episode, title_hun, title_orig, release_date, description, length, img_url, video_url, user_id, status, created_date, " +
                    "last_modified_date FROM episodes WHERE " + status + " id=@episode_id ORDER BY episode";
                command.Parameters.Add(new SqlParameter("@episode_id", SqlDbType.Int, 4)).Value = episode_id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        s.id = Convert.ToInt32(sdr["id"]);
                        s.seriesId = Convert.ToInt32(sdr["series_id"]);
                        s.seasonId = Convert.ToInt32(sdr["season_id"]);
                        s.episode = Convert.ToInt32(sdr["episode"]);
                        s.titleHun = sdr["title_hun"].ToString();
                        if (sdr["title_orig"] != null) s.titleOrig = sdr["title_orig"].ToString();
                        if (sdr["release_date"] != null) s.releaseDate = Convert.ToDateTime(sdr["release_date"]);
                        if (sdr["description"] != null) s.description = sdr["description"].ToString();
                        if (sdr["length"] != null) s.length = Convert.ToInt32(sdr["length"]);
                        if (sdr["img_url"] != null) s.imgUrl = sdr["img_url"].ToString();
                        if (sdr["video_url"] != null) s.videoUrl = sdr["video_url"].ToString();
                        s.userID = Convert.ToInt32(sdr["user_id"]);
                        s.status = Convert.ToInt32(sdr["status"]);
                        s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                        s.lastModifiedDate = Convert.ToDateTime(sdr["last_modified_date"]);
                    }
                    sdr.Close();
                }

                return s;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean InsertSeries(Serie s, int user_id, bool isAdmin)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();
            SqlTransaction transaction = null;

            try
            {
                string status = "";
                string kukac_status = "";
                if (isAdmin)
                {
                    status = ",status";
                    kukac_status = ", @status";
                }
                // BeginTransaction() Requires Open Connection
                connection.Open();
                transaction = connection.BeginTransaction();
                // Assign Transaction to Command
                command.Transaction = transaction;
                command.CommandText = "SELECT max(id) FROM series WITH (xlock)";
                int id = -1;
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        id = sdr.GetInt32(0);
                    }
                    sdr.Close();
                }
                id += 1;

                command.CommandText = "SET IDENTITY_INSERT series ON";
                command.ExecuteNonQuery();

                command.CommandText =
                    "INSERT INTO series (id,title_hun,title_orig,directors,writers,stars,description,release_date,img_url,video_url,user_id" + status + ") " +
                    "VALUES (@id, @title_hun, @title_orig, @directors, @writers, @stars, @description, @release_date, @img_url, @video_url, @user_id" + kukac_status + ")";
                command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4)).Value = id;
                command.Parameters.Add(new SqlParameter("@title_hun", SqlDbType.VarChar, 100)).Value = s.titleHun;
                command.Parameters.Add(new SqlParameter("@title_orig", SqlDbType.VarChar, 100)).Value = s.titleOrig;
                command.Parameters.Add(new SqlParameter("@directors", SqlDbType.Text, s.directors.Length + 1)).Value = s.directors;
                command.Parameters.Add(new SqlParameter("@writers", SqlDbType.Text, s.writers.Length + 1)).Value = s.writers;
                command.Parameters.Add(new SqlParameter("@stars", SqlDbType.Text, s.stars.Length + 1)).Value = s.stars;
                command.Parameters.Add(new SqlParameter("@description", SqlDbType.Text, s.description.Length + 1)).Value = s.description;
                command.Parameters.Add(new SqlParameter("@release_date", SqlDbType.DateTime, 8)).Value = s.releaseDate;
                command.Parameters.Add(new SqlParameter("@img_url", SqlDbType.VarChar, 255)).Value = s.imgUrl;
                command.Parameters.Add(new SqlParameter("@video_url", SqlDbType.VarChar, 255)).Value = s.videoUrl;
                command.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int, 4)).Value = user_id;
                command.Parameters.Add(new SqlParameter("@status", SqlDbType.Int, 4)).Value = 1;
                // Call Prepare after setting the Commandtext and Parameters.
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "SET IDENTITY_INSERT series OFF";
                command.ExecuteNonQuery();

                int count = 0;
                foreach (ListItem genre in s.genres)
                {
                    count++;
                    string sid = "@series_id" + count.ToString();
                    string gid = "@genre_id" + count.ToString();
                    command.CommandText = "INSERT INTO genre_index (series_id, genre_id) VALUES (" + sid + "," + gid + ")";
                    command.Parameters.Add(new SqlParameter(sid, SqlDbType.Int, 4)).Value = id;
                    command.Parameters.Add(new SqlParameter(gid, SqlDbType.Int, 4)).Value = Convert.ToInt32(genre.Value);
                    // Call Prepare after setting the Commandtext and Parameters.
                    command.Prepare();
                    command.ExecuteNonQuery();
                }
                transaction.Commit();

                return true;
            }
            catch (Exception e)
            {
                transaction.Rollback();
                Alert.Show("Error while creating series! " + e.ToString());
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean UpdateSeries(Serie s, bool change_img)
    {
        ListItemCollection retVal = new ListItemCollection();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();
            SqlTransaction transaction = null;

            try
            {
                // BeginTransaction() Requires Open Connection
                connection.Open();
                transaction = connection.BeginTransaction();
                // Assign Transaction to Command
                command.Transaction = transaction;

                string img_url = "";
                if (change_img) {
                    img_url = "img_url=@img_url,";
                }

                command.CommandText =
                    "UPDATE series SET title_hun=@title_hun, title_orig=@title_orig, directors=@directors, writers=@writers, stars=@stars, " +
                    "description=@description, release_date=@release_date," + img_url + " video_url=@video_url, last_modified_date=@last_modified_date WHERE id=@id";
                command.Parameters.Add(new SqlParameter("@title_hun", SqlDbType.VarChar, 100)).Value = s.titleHun;
                command.Parameters.Add(new SqlParameter("@title_orig", SqlDbType.VarChar, 100)).Value = s.titleOrig;
                command.Parameters.Add(new SqlParameter("@directors", SqlDbType.Text, s.directors.Length + 1)).Value = s.directors;
                command.Parameters.Add(new SqlParameter("@writers", SqlDbType.Text, s.writers.Length + 1)).Value = s.writers;
                command.Parameters.Add(new SqlParameter("@stars", SqlDbType.Text, s.stars.Length + 1)).Value = s.stars;
                command.Parameters.Add(new SqlParameter("@description", SqlDbType.Text, s.description.Length + 1)).Value = s.description;
                command.Parameters.Add(new SqlParameter("@release_date", SqlDbType.DateTime, 8)).Value = s.releaseDate;
                command.Parameters.Add(new SqlParameter("@img_url", SqlDbType.VarChar, 255)).Value = s.imgUrl;
                command.Parameters.Add(new SqlParameter("@video_url", SqlDbType.VarChar, 255)).Value = s.videoUrl;
                command.Parameters.Add(new SqlParameter("@last_modified_date", SqlDbType.DateTime, 8)).Value = DateTime.Now;
                command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4)).Value = s.id;
                // Call Prepare after setting the Commandtext and Parameters.
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM genre_index WHERE genre_index.series_id=@series_id";
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int)).Value = s.id;
                command.Prepare();
                command.ExecuteNonQuery();

                int count = 0;
                foreach (ListItem genre in s.genres)
                {
                    count++;
                    string sid = "@series_id" + count.ToString();
                    string gid = "@genre_id" + count.ToString();
                    command.CommandText = "INSERT INTO genre_index (series_id, genre_id) VALUES (" + sid + "," + gid + ")";
                    command.Parameters.Add(new SqlParameter(sid, SqlDbType.Int, 4)).Value = s.id;
                    command.Parameters.Add(new SqlParameter(gid, SqlDbType.Int, 4)).Value = Convert.ToInt32(genre.Value);
                    // Call Prepare after setting the Commandtext and Parameters.
                    command.Prepare();
                    command.ExecuteNonQuery();
                }
                transaction.Commit();

                return true;
            }
            catch (Exception e)
            {
                transaction.Rollback();
                Alert.Show("Error while updating series! " + e.ToString());
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean UpdateSeriesStatus(int id, int status)
    {
        ListItemCollection retVal = new ListItemCollection();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                // BeginTransaction() Requires Open Connection
                connection.Open();

                command.CommandText = "UPDATE series SET status=@status WHERE id=@id";
                command.Parameters.Add(new SqlParameter("@status", SqlDbType.Int, 4)).Value = status;
                command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4)).Value = id;
                // Call Prepare after setting the Commandtext and Parameters.
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception e)
            {
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean DeleteSeries(int id)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();
            SqlTransaction transaction = null;

            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                command.Transaction = transaction;

                command.CommandText = "DELETE FROM ratings WHERE ratings.series_id=@sid1";
                command.Parameters.Add(new SqlParameter("@sid1", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM comments WHERE comments.series_id=@sid2";
                command.Parameters.Add(new SqlParameter("@sid2", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM genre_index WHERE genre_index.series_id=@sid3";
                command.Parameters.Add(new SqlParameter("@sid3", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM episodes WHERE series_id=@sid4";
                command.Parameters.Add(new SqlParameter("@sid4", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM seasons WHERE series_id=@sid5";
                command.Parameters.Add(new SqlParameter("@sid5", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM series WHERE id=@sid6";
                command.Parameters.Add(new SqlParameter("@sid6", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                transaction.Commit();

                return true;
            }
            catch (Exception e)
            {
                transaction.Rollback();
                Alert.Show("Error while deleting series! " + e.ToString());
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean InsertSeason(Season s, int user_id, bool isAdmin)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();

                string status = "";
                string kukac_status = "";
                if (isAdmin)
                {
                    status = ",status";
                    kukac_status = ", @status";
                }

                command.CommandText =
                    "INSERT INTO seasons (series_id,season,description,release_date,img_url,video_url,user_id" + status + ") " +
                    "VALUES (@series_id, @season, @description, @release_date, @img_url, @video_url, @user_id" + kukac_status + ")";
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int, 4)).Value = s.seriesId;
                command.Parameters.Add(new SqlParameter("@season", SqlDbType.Int, 4)).Value = s.season;
                command.Parameters.Add(new SqlParameter("@description", SqlDbType.Text, s.description.Length + 1)).Value = s.description;
                command.Parameters.Add(new SqlParameter("@release_date", SqlDbType.DateTime, 8)).Value = s.releaseDate;
                command.Parameters.Add(new SqlParameter("@img_url", SqlDbType.VarChar, 255)).Value = s.imgUrl;
                command.Parameters.Add(new SqlParameter("@video_url", SqlDbType.VarChar, 255)).Value = s.videoUrl;
                command.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int, 4)).Value = user_id;
                command.Parameters.Add(new SqlParameter("@status", SqlDbType.Int, 4)).Value = 1;
                // Call Prepare after setting the Commandtext and Parameters.
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception e)
            {
                Alert.Show("Error while creating season! " + e.ToString());
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean UpdateSeason(Season s, bool change_img)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();

                string img_url = "";
                if (change_img) {
                    img_url = "img_url=@img_url,";
                }

                command.CommandText =
                    "UPDATE seasons SET series_id=@series_id, season=@season, description=@description, release_date=@release_date," + img_url + " video_url=@video_url, last_modified_date=@last_modified_date WHERE id=@id";
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int, 4)).Value = s.seriesId;
                command.Parameters.Add(new SqlParameter("@season", SqlDbType.Int, 4)).Value = s.season;
                command.Parameters.Add(new SqlParameter("@description", SqlDbType.Text, s.description.Length + 1)).Value = s.description;
                command.Parameters.Add(new SqlParameter("@release_date", SqlDbType.DateTime, 8)).Value = s.releaseDate;
                command.Parameters.Add(new SqlParameter("@img_url", SqlDbType.VarChar, 255)).Value = s.imgUrl;
                command.Parameters.Add(new SqlParameter("@video_url", SqlDbType.VarChar, 255)).Value = s.videoUrl;
                command.Parameters.Add(new SqlParameter("@last_modified_date", SqlDbType.DateTime, 8)).Value = DateTime.Now;
                command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4)).Value = s.id;
                // Call Prepare after setting the Commandtext and Parameters.
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception e)
            {
                Alert.Show("Error while updating season! " + e.ToString());
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean UpdateSeasonStatus(int id, int status)
    {
        ListItemCollection retVal = new ListItemCollection();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                // BeginTransaction() Requires Open Connection
                connection.Open();

                command.CommandText = "UPDATE seasons SET status=@status WHERE id=@id";
                command.Parameters.Add(new SqlParameter("@status", SqlDbType.Int, 4)).Value = status;
                command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4)).Value = id;
                // Call Prepare after setting the Commandtext and Parameters.
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception e)
            {
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean DeleteSeason(int id)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();
            SqlTransaction transaction = null;

            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                command.Transaction = transaction;

                command.CommandText = "DELETE FROM ratings WHERE ratings.season_id=@sid1";
                command.Parameters.Add(new SqlParameter("@sid1", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM comments WHERE comments.season_id=@sid2";
                command.Parameters.Add(new SqlParameter("@sid2", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM episodes WHERE season_id=@sid3";
                command.Parameters.Add(new SqlParameter("@sid3", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM seasons WHERE id=@sid4";
                command.Parameters.Add(new SqlParameter("@sid4", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                transaction.Commit();

                return true;
            }
            catch (Exception e)
            {
                transaction.Rollback();
                Alert.Show("Error while deleting season! " + e.ToString());
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean InsertEpisode(Episode s, int user_id, bool isAdmin)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();


                string status = "";
                string kukac_status = "";
                if (isAdmin)
                {
                    status = ",status";
                    kukac_status = ", @status";
                }
                
                command.CommandText =
                    "INSERT INTO episodes (series_id,season_id,episode,title_hun,title_orig,description,release_date,length,img_url,video_url,user_id" + status + ") " +
                    "VALUES (@series_id, @season_id, @episode, @title_hun, @title_orig, @description, @release_date, @length, @img_url, @video_url, @user_id" + kukac_status + ")";
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int, 4)).Value = s.seriesId;
                command.Parameters.Add(new SqlParameter("@season_id", SqlDbType.Int, 4)).Value = s.seasonId;
                command.Parameters.Add(new SqlParameter("@episode", SqlDbType.Int, 4)).Value = s.episode;
                command.Parameters.Add(new SqlParameter("@title_hun", SqlDbType.VarChar, 100)).Value = s.titleHun;
                command.Parameters.Add(new SqlParameter("@title_orig", SqlDbType.VarChar, 100)).Value = s.titleOrig;
                command.Parameters.Add(new SqlParameter("@description", SqlDbType.Text, s.description.Length + 1)).Value = s.description;
                command.Parameters.Add(new SqlParameter("@release_date", SqlDbType.DateTime, 8)).Value = s.releaseDate;
                command.Parameters.Add(new SqlParameter("@length", SqlDbType.Int, 6)).Value = s.length;
                command.Parameters.Add(new SqlParameter("@img_url", SqlDbType.VarChar, 255)).Value = s.imgUrl;
                command.Parameters.Add(new SqlParameter("@video_url", SqlDbType.VarChar, 255)).Value = s.videoUrl;
                command.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int, 4)).Value = user_id;
                command.Parameters.Add(new SqlParameter("@status", SqlDbType.Int, 4)).Value = 1;
                // Call Prepare after setting the Commandtext and Parameters.
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception e)
            {
                Alert.Show("Error while creating episode! " + e.ToString());
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean UpdateEpisode(Episode s, bool change_img)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();


                string img_url = "";
                if (change_img)
                {
                    img_url = "img_url=@img_url,";
                }

                command.CommandText =
                    "UPDATE episodes SET series_id=@series_id, season_id=@season_id, episode=@episode, title_hun=@title_hun, title_orig=@title_orig, " +
                    "description=@description, release_date=@release_date, length=@length," + img_url + " video_url=@video_url, last_modified_date=@last_modified_date WHERE id=@id";
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int, 4)).Value = s.seriesId;
                command.Parameters.Add(new SqlParameter("@season_id", SqlDbType.Int, 4)).Value = s.seasonId;
                command.Parameters.Add(new SqlParameter("@episode", SqlDbType.Int, 4)).Value = s.episode;
                command.Parameters.Add(new SqlParameter("@title_hun", SqlDbType.VarChar, 100)).Value = s.titleHun;
                command.Parameters.Add(new SqlParameter("@title_orig", SqlDbType.VarChar, 100)).Value = s.titleOrig;
                command.Parameters.Add(new SqlParameter("@description", SqlDbType.Text, s.description.Length + 1)).Value = s.description;
                command.Parameters.Add(new SqlParameter("@release_date", SqlDbType.DateTime, 8)).Value = s.releaseDate;
                command.Parameters.Add(new SqlParameter("@length", SqlDbType.Int, 6)).Value = s.length;
                command.Parameters.Add(new SqlParameter("@img_url", SqlDbType.VarChar, 255)).Value = s.imgUrl;
                command.Parameters.Add(new SqlParameter("@video_url", SqlDbType.VarChar, 255)).Value = s.videoUrl;
                command.Parameters.Add(new SqlParameter("@last_modified_date", SqlDbType.DateTime, 8)).Value = DateTime.Now;
                command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4)).Value = s.id;
                // Call Prepare after setting the Commandtext and Parameters.
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception e)
            {
                Alert.Show("Error while updating episode! " + e.ToString());
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean UpdateEpisodeStatus(int id, int status)
    {
        ListItemCollection retVal = new ListItemCollection();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                // BeginTransaction() Requires Open Connection
                connection.Open();

                command.CommandText = "UPDATE episodes SET status=@status WHERE id=@id";
                command.Parameters.Add(new SqlParameter("@status", SqlDbType.Int, 4)).Value = status;
                command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4)).Value = id;
                // Call Prepare after setting the Commandtext and Parameters.
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception e)
            {
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean DeleteEpisode(int id)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();
            SqlTransaction transaction = null;

            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                command.Transaction = transaction;

                command.CommandText = "DELETE FROM ratings WHERE ratings.episode_id=@eid1";
                command.Parameters.Add(new SqlParameter("@eid1", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM comments WHERE comments.episode_id=@eid2";
                command.Parameters.Add(new SqlParameter("@eid2", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM episodes WHERE id=@eid3";
                command.Parameters.Add(new SqlParameter("@eid3", SqlDbType.Int)).Value = id;
                command.Prepare();
                command.ExecuteNonQuery();

                transaction.Commit();

                return true;
            }
            catch (Exception e)
            {
                transaction.Rollback();
                Alert.Show("Error while deleting episode! " + e.ToString());
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public String GetUserById(int user_id)
    {
        String userName = "Anonymus";

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                command.CommandText = "SELECT username FROM users WHERE id=@user_id";
                command.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int, 4)).Value = user_id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        userName = sdr["username"].ToString();
                    }
                    sdr.Close();
                }
                return userName;
            }
            catch (Exception)
            {
                return userName;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean UpdateUserStatus(int user_id, int status)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();
            try
            {
                connection.Open();
                command.CommandText = "UPDATE users SET status=@status WHERE id=@user_id";
                command.Parameters.Add(new SqlParameter("@status", SqlDbType.Int, 4)).Value = status;
                command.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int, 4)).Value = user_id;
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean DeleteUser(int user_id)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();
            try
            {
                connection.Open();
                command.CommandText = "DELETE FROM users WHERE id=@user_id";
                command.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int, 4)).Value = user_id;
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public int GetUserStatusById(int user_id)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();
            int status = -2;
            try
            {
                connection.Open();
                command.CommandText = "SELECT status FROM users WHERE id=@user_id";
                command.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int, 4)).Value = user_id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        status = Convert.ToInt32(sdr["status"]);
                    }
                    sdr.Close();
                }
                return status;
            }
            catch (Exception)
            {
                return -2;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public List<Comment> GetComments(int series_id, int season_id, int episode_id)
    {
        List<Comment> comments = new List<Comment>();

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                command.CommandText = "SELECT id,title,content,series_id,season_id,episode_id,user_id,created_date," +
                    "last_modified_date FROM comments WHERE status=1 AND series_id=@series_id AND season_id=@season_id AND episode_id=@episode_id ORDER BY created_date";
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int, 4)).Value = series_id;
                command.Parameters.Add(new SqlParameter("@season_id", SqlDbType.Int, 4)).Value = season_id;
                command.Parameters.Add(new SqlParameter("@episode_id", SqlDbType.Int, 4)).Value = episode_id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        Comment c = new Comment();
                        c.id = Convert.ToInt32(sdr["id"]);
                        if (sdr["title"] != null) c.title = sdr["title"].ToString();
                        c.content = sdr["content"].ToString();
                        c.seriesId = Convert.ToInt32(sdr["series_id"]);
                        c.seasonId = Convert.ToInt32(sdr["season_id"]);
                        c.episodeId = Convert.ToInt32(sdr["episode_id"]);
                        c.userId = Convert.ToInt32(sdr["user_id"]);
                        c.createdDate = Convert.ToDateTime(sdr["created_date"]);
                        c.lastModifiedDate = Convert.ToDateTime(sdr["last_modified_date"]);
                        comments.Add(c);
                    }
                    sdr.Close();
                }
                return comments;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean InsertComment(Comment c)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();

                command.CommandText =
                    "INSERT INTO comments (title,content,series_id,season_id,episode_id,user_id,status) " +
                    "VALUES (@title, @content, @series_id, @season_id, @episode_id, @user_id,@status)";
                command.Parameters.Add(new SqlParameter("@title", SqlDbType.VarChar, 255)).Value = c.title;
                command.Parameters.Add(new SqlParameter("@content", SqlDbType.VarChar, c.content.Length + 1)).Value = c.content;
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int, 4)).Value = c.seriesId;
                command.Parameters.Add(new SqlParameter("@season_id", SqlDbType.Int, 4)).Value = c.seasonId;
                command.Parameters.Add(new SqlParameter("@episode_id", SqlDbType.Int, 4)).Value = c.episodeId;
                command.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int, 4)).Value = c.userId;
                command.Parameters.Add(new SqlParameter("@status", SqlDbType.Int, 4)).Value = 1;
                // Call Prepare after setting the Commandtext and Parameters.
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception e)
            {
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }
    
    public String AuthenticateUser(string username, string password)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();
            String matchFount = null;
            try
            {
                connection.Open();
                command.CommandText = "SELECT id FROM users WHERE status=1 AND username=@username AND password=@password";
                command.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 100)).Value = username;
                command.Parameters.Add(new SqlParameter("@password", SqlDbType.VarChar, 100)).Value = password;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        matchFount = sdr["id"].ToString();
                    }
                    sdr.Close();
                }
                return matchFount;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public String GetUserType(int id)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();
            String matchFount = null;
            try
            {
                connection.Open();
                command.CommandText = "SELECT role FROM users WHERE id=@id";
                command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4)).Value = id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        matchFount = sdr["role"].ToString();
                    }
                    sdr.Close();
                }
                return matchFount;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public String InsertUser(string username, string email, string password, string avatar, bool isAdmin)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();

                string admin = "";
                string kukac_admin = "";
                if (isAdmin) {
                    admin = ",role,status";
                    kukac_admin = ", @role, @status";
                }

                command.CommandText =
                    "INSERT INTO users (username,password,email,avatar" + admin + ") " +
                    "VALUES (@username, @password, @email, @avatar" + kukac_admin + ")";
                command.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 100)).Value = username;
                command.Parameters.Add(new SqlParameter("@password", SqlDbType.VarChar, 100)).Value = password;
                command.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 255)).Value = email;
                command.Parameters.Add(new SqlParameter("@avatar", SqlDbType.VarChar, 255)).Value = avatar;
                command.Parameters.Add(new SqlParameter("@admin", SqlDbType.VarChar, 10)).Value = "admin";
                command.Parameters.Add(new SqlParameter("@status", SqlDbType.Int, 4)).Value = 1;
                // Call Prepare after setting the Commandtext and Parameters.
                command.Prepare();
                command.ExecuteNonQuery();

                return "OK";
            }
            catch (SqlException e)
            {
                return e.Number.ToString();
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public List<Search> Search(string prefix)
    {

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            List<Search> result = new List<Search>();

            try
            {
                connection.Open();
                string searchText = "%" + prefix + "%";
                command.CommandText = "SELECT DISTINCT(series.id), series.title_hun as text FROM genre_index RIGHT OUTER JOIN series ON series.id = genre_index.series_id " +
                        "LEFT OUTER JOIN genres ON genres.id = genre_index.genre_id WHERE (title_hun LIKE @text OR title_orig LIKE @text OR description LIKE @text " +
                        "OR directors LIKE @text OR writers LIKE @text OR stars LIKE @text OR genre_hu LIKE @text) AND series.status = 1";
                command.Parameters.Add(new SqlParameter("@text", SqlDbType.VarChar, 100)).Value = searchText;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        result.Add(new Search(Convert.ToInt32(sdr["id"]), (String)sdr["text"], "sorozat"));
                    }
                    sdr.Close();
                }

                command.CommandText = "SELECT DISTINCT(seasons.id), (series.title_hun + ' - ' + CAST( season as varchar(10)) + '. Évad') as text " +
                    "FROM seasons INNER JOIN series ON series.id = seasons.series_id WHERE (CAST( season as varchar(10)) LIKE @text2 OR seasons.description LIKE @text2) AND seasons.status = 1";
                command.Parameters.Add(new SqlParameter("@text2", SqlDbType.VarChar, 100)).Value = searchText;
                command.Prepare();
                sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        result.Add(new Search(Convert.ToInt32(sdr["id"]), (String)sdr["text"], "evad"));
                    }
                    sdr.Close();
                }

                command.CommandText = "SELECT DISTINCT(episodes.id), (series.title_hun + ' - ' + CAST( season as varchar(10)) + '. Évad' + ' - ' + CAST( episode as varchar(10)) + '.Epizód (' + episodes.title_hun + ')') as text, episodes.content_type " +
                    "FROM episodes INNER JOIN seasons ON seasons.id = episodes.season_id INNER JOIN series ON series.id = episodes.series_id WHERE ( CAST( episode as varchar(10)) LIKE @text3 OR episodes.title_hun LIKE @text3 " +
                    "OR episodes.title_orig LIKE @text3 OR episodes.description LIKE @text3 ) AND episodes.status = 1";
                command.Parameters.Add(new SqlParameter("@text3", SqlDbType.VarChar, 100)).Value = searchText;
                command.Prepare();
                sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        result.Add(new Search(Convert.ToInt32(sdr["id"]), (String)sdr["text"], "epizod"));
                    }
                    sdr.Close();
                }

                return result;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public List<Content> SearchAdminList(bool series, bool seasons, bool episodes, int status, bool statusflag)
    {

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            List<Content> result = new List<Content>();

            try
            {
                connection.Open();
                string whereString = "";
                SqlDataReader sdr;
                if (series)
                {
                    whereString = "";
                    if (statusflag)
                        whereString = " WHERE status=@status1";
                    command.CommandText = "SELECT id, title_hun, created_date, user_id, status FROM series " + whereString;
                    command.Parameters.Add(new SqlParameter("@status1", SqlDbType.Int, 4)).Value = status;
                    command.Prepare();
                    sdr = command.ExecuteReader();
                    if (sdr != null)
                    {
                        while (sdr.Read())
                        {
                            Content s = new Content();
                            s.id = Convert.ToInt32(sdr["id"]);
                            s.seriesTitle = sdr["title_hun"].ToString();
                            s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                            s.userId = Convert.ToInt32(sdr["user_id"]);
                            s.status = Convert.ToInt32(sdr["status"]);
                            s.type = "sorozat";
                            result.Add(s);
                        }
                        sdr.Close();
                    }
                }
                if (seasons)
                {
                    whereString = "";
                    if (statusflag)
                        whereString = " WHERE seasons.status=@status2";
                    command.CommandText = "SELECT seasons.id, series.title_hun, seasons.season, seasons.created_date, seasons.user_id, seasons.status " +
                        "FROM seasons INNER JOIN series ON series.id = seasons.series_id " + whereString;
                    command.Parameters.Add(new SqlParameter("@status2", SqlDbType.Int, 4)).Value = status;
                    command.Prepare();
                    sdr = command.ExecuteReader();
                    if (sdr != null)
                    {
                        while (sdr.Read())
                        {
                            Content s = new Content();
                            s.id = Convert.ToInt32(sdr["id"]);
                            s.seriesTitle = sdr["title_hun"].ToString();
                            s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                            s.userId = Convert.ToInt32(sdr["user_id"]);
                            s.status = Convert.ToInt32(sdr["status"]);
                            s.season = Convert.ToInt32(sdr["season"]);
                            s.type = "evad";
                            result.Add(s);
                        }
                        sdr.Close();
                    }
                }
                if (episodes)
                {
                    whereString = "";
                    if (statusflag)
                        whereString = " WHERE episodes.status=@status3";
                    command.CommandText = "SELECT episodes.id as id, series.title_hun as stitle_hun, seasons.season , episodes.title_hun as etitle_hun, episodes.episode, episodes.created_date, episodes.user_id, episodes.status " +
                        "FROM episodes INNER JOIN seasons ON seasons.id = episodes.season_id INNER JOIN series ON series.id = episodes.series_id " + whereString;
                    command.Parameters.Add(new SqlParameter("@status3", SqlDbType.Int, 4)).Value = status;
                    command.Prepare();
                    sdr = command.ExecuteReader();
                    if (sdr != null)
                    {
                        while (sdr.Read())
                        {
                            Content s = new Content();
                            s.id = Convert.ToInt32(sdr["id"]);
                            s.seriesTitle = sdr["stitle_hun"].ToString();
                            s.episodeTitle = sdr["etitle_hun"].ToString();
                            s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                            s.userId = Convert.ToInt32(sdr["user_id"]);
                            s.status = Convert.ToInt32(sdr["status"]);
                            s.season = Convert.ToInt32(sdr["season"]);
                            s.episode = Convert.ToInt32(sdr["episode"]);
                            s.type = "epizod";
                            result.Add(s);
                        }
                        sdr.Close();
                    }
                }
                return result;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public List<User> SearchAdminListUser(bool admin, bool user, int status, bool statusflag)
    {

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            List<User> result = new List<User>();

            try
            {
                connection.Open();
                string whereString = "";
                string role = "";
                if (statusflag && admin && user)
                    whereString = " WHERE status=@status";
                else if (!statusflag && (!admin || !user))
                    whereString = " WHERE role=@role";
                else if (statusflag && (!admin || !user))
                    whereString = " WHERE status=@status AND role=@role";
                if (admin)
                    role = "admin";
                else
                    role = "user";
                command.CommandText = "SELECT id, username, role, status, reg_date, email FROM users " + whereString + " ORDER BY reg_date DESC";
                command.Parameters.Add(new SqlParameter("@status", SqlDbType.Int, 4)).Value = status;
                command.Parameters.Add(new SqlParameter("@role", SqlDbType.VarChar, 10)).Value = role;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        User s = new User();
                        s.id = Convert.ToInt32(sdr["id"]);
                        s.username = sdr["username"].ToString();
                        s.role = sdr["role"].ToString();
                        s.status = Convert.ToInt32(sdr["status"]);
                        s.regDate = Convert.ToDateTime(sdr["reg_date"]);
                        s.email = sdr["email"].ToString();
                        result.Add(s);
                    }
                    sdr.Close();
                }

                return result;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public List<Subtitle> GetSubtitlesPreview()
    {
        List<Subtitle> subtitles = new List<Subtitle>();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                command.CommandText = "SELECT TOP 50 subtitles.id, series, season, episode, sub_url, language, user_id, " +
                    "created_date, last_modified_date FROM (subtitles INNER JOIN languages ON language_id = languages.id) " +
                    "WHERE status=1 ORDER BY last_modified_date DESC";
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        Subtitle s = new Subtitle();
                        s.id = Convert.ToInt32(sdr["id"]);
                        if (sdr["series"] != null) s.series = sdr["series"].ToString();
                        if (sdr["season"] != null) s.season = Convert.ToInt32(sdr["season"]);
                        if (sdr["episode"] != null) s.episode = Convert.ToInt32(sdr["episode"]);
                        s.subUrl = sdr["sub_url"].ToString();
                        if (sdr["language"] != null) s.language = sdr["language"].ToString();
                        s.userID = Convert.ToInt32(sdr["user_id"]);
                        s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                        s.lastModifiedDate = Convert.ToDateTime(sdr["last_modified_date"]);
                        subtitles.Add(s);
                    }
                    sdr.Close();
                }
                return subtitles;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public List<Subtitle> GetSearchedSubtitles(String searchbox, String seriesValue, int seasonValue, int episodeValue)
    {
        List<Subtitle> subtitles = new List<Subtitle>();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                String condition = "";
                if (searchbox != "")
                {
                    condition = " AND sub_url LIKE '%" + searchbox + "%' ";
                }
                if (seriesValue != "")
                {
                    condition += " AND series=@series ";
                    if (seasonValue != 0)
                    {
                        condition += " AND season=@season ";
                        if (episodeValue != 0)
                        {
                            condition += " AND episode=@episode ";
                        }

                    }

                }
                connection.Open();
                command.CommandText = "SELECT subtitles.id, series, season, episode, sub_url, language, user_id, created_date, last_modified_date " +
                    "FROM (subtitles INNER JOIN languages ON language_id = languages.id) " + 
                    "WHERE status=1 " + condition + " ORDER BY last_modified_date DESC";
                command.Parameters.Add(new SqlParameter("@series", SqlDbType.VarChar, 255)).Value = seriesValue;
                command.Parameters.Add(new SqlParameter("@season", SqlDbType.Int, 4)).Value = seasonValue;
                command.Parameters.Add(new SqlParameter("@episode", SqlDbType.Int, 4)).Value = episodeValue;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        Subtitle s = new Subtitle();
                        s.id = Convert.ToInt32(sdr["id"]);
                        if (sdr["series"] != null) s.series = sdr["series"].ToString();
                        if (sdr["season"] != null) s.season = Convert.ToInt32(sdr["season"]);
                        if (sdr["episode"] != null) s.episode = Convert.ToInt32(sdr["episode"]);
                        s.subUrl = sdr["sub_url"].ToString();
                        s.language = sdr["language"].ToString();
                        s.userID = Convert.ToInt32(sdr["user_id"]);
                        s.createdDate = Convert.ToDateTime(sdr["created_date"]);
                        s.lastModifiedDate = Convert.ToDateTime(sdr["last_modified_date"]);
                        subtitles.Add(s);
                    }
                    sdr.Close();
                }
                return subtitles;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public ListItemCollection GetLanguages()
    {
        ListItemCollection retVal = new ListItemCollection();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT id, language FROM languages ORDER BY language", connection);
                connection.Open();
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        string id = sdr.GetInt32(0).ToString();
                        string language = sdr.GetString(1);
                        retVal.Add(new ListItem(language, id));
                    }
                }
            }
            catch (Exception e)
            {
                Alert.Show("Error in connecting! " + e.ToString());
                return null;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
        return retVal;
    }

    public Boolean InsertSubtitle(Subtitle s, int user_id)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();

                command.CommandText = "INSERT INTO subtitles (series, season, episode, sub_url, language_id, user_id) " +
                "VALUES (@series, @season, @episode, @sub_url, " +
                    "(SELECT languages.id FROM languages WHERE language = @language), @user_id)";
                command.Parameters.Add(new SqlParameter("@series", SqlDbType.VarChar, 255)).Value = s.series;
                command.Parameters.Add(new SqlParameter("@season", SqlDbType.Int, 4)).Value = s.season;
                command.Parameters.Add(new SqlParameter("@episode", SqlDbType.Int, 4)).Value = s.episode;
                command.Parameters.Add(new SqlParameter("@sub_url", SqlDbType.VarChar, 255)).Value = s.subUrl;
                command.Parameters.Add(new SqlParameter("@language", SqlDbType.VarChar, 25)).Value = s.language;
                command.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int, 4)).Value = user_id;
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception e)
            {
                Alert.Show("Error while creating subtitle! " + e.ToString());
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public int GetRating(int series_id, int season_id, int episode_id, int user_id)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            int rating = -1;
            SqlCommand command = connection.CreateCommand();
            try
            {
                connection.Open();
                //string where = "";
                //if (season_id > 0) {
                //    where = " AND season_id=@season_id";
                //    if(episode_id > 0)
                //        where += " AND episode_id=@episode_id";
                //}
                command.CommandText = "SELECT stars FROM rating WHERE user_id=@user_id AND series_id=@series_id AND season_id=@season_id AND episode_id=@episode_id";
                command.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int, 4)).Value = user_id;
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int, 4)).Value = series_id;
                command.Parameters.Add(new SqlParameter("@season_id", SqlDbType.Int, 4)).Value = season_id;
                command.Parameters.Add(new SqlParameter("@episode_id", SqlDbType.Int, 4)).Value = episode_id;
                command.Prepare();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        rating = Convert.ToInt32(sdr["stars"]);
                    }
                    sdr.Close();
                }
                return rating;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }

    public Boolean InsertRating(int rating, int series_id, int season_id, int episode_id, int user_id)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSeriesDB"].ToString()))
        {
            SqlCommand command = connection.CreateCommand();

            try
            {
                connection.Open();
                command.CommandText = "INSERT INTO rating (stars, series_id, season_id, episode_id, user_id) " +
                "VALUES (@stars, @series_id, @season_id, @episode_id, @user_id)";
                command.Parameters.Add(new SqlParameter("@stars", SqlDbType.Int, 4)).Value = rating;
                command.Parameters.Add(new SqlParameter("@series_id", SqlDbType.Int, 4)).Value = series_id;
                command.Parameters.Add(new SqlParameter("@season_id", SqlDbType.Int, 4)).Value = season_id;
                command.Parameters.Add(new SqlParameter("@episode_id", SqlDbType.Int, 4)).Value = episode_id;
                command.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int, 4)).Value = user_id;
                command.Prepare();
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                connection.Close();
                //connection.Dispose();
            }
        }
    }
}

